
/*program:	CFI_Model_v01.sql created March 11, 2025
			Versioned using SQL (SSMS 20.2.30.0) based upon original SAS code Published May 25, 2021 by

			Kim, Dae Hyun; Gautam, Nileesa, 2020, "SAS Programs - Claims-Based Frailty Index", 
			https://doi.org/10.7910/DVN/HM8DOI, Harvard Dataverse, V15, UNF:6:bvNfdccNq1mGW3HZBUY8YA== [fileUNF]

  authors:	Jason Wachsmuth (self) and Ryan Murphy (Evernorth Health Services)

  credits:	Kim, Dae Hyun; Gautam, Nileesa; Bedell, Doug; Tambellini, Vince for their earlier work
  
  inputs:	dx9, dx10, cpt/hcpcs, and weight look up files
			patient id list
			patient dx9, dx10, and hcpcs/cpt sample claims
			
  output:	frailty score per patient

  notes:	uses master database, directory defined as 'c:\cfi_sql\' and occurs 9 times including this line
  			used the original files downloaded from 'R Program - Claims-Based Frailty Index' by Doug Bedell (Westat) as input
				-filenames deviate from the orginals to be more descriptive but contents are exactly the same
				-SQL patient score results were replicated with R code*/



--read in reference look up tables
create table [master].[dbo].[lu_dx9] --(6960 rows affected)
	(disease_number	char(2)
	,dx				char(5)
	,description	char(25)
	,icd_ver		char(10))
  bulk insert [master].[dbo].[lu_dx9]
  from 'c:\cfi_sql\lu_CFI_ICD9CM_V32.csv'		with (format = 'csv', firstrow = 2,  fieldterminator=',',	rowterminator = '\n',	fieldquote = '"')

create table [master].[dbo].[lu_dx10] --(27621 rows affected)
	(disease_number	char(2)
	,dx				char(7)
	,description	char(70)
	,icd_ver		char(12))
  bulk insert [master].[dbo].[lu_dx10]
  from 'c:\cfi_sql\lu_CFI_ICD10CM_V2020.csv'	with (format = 'csv', firstrow = 2,  fieldterminator=',',	rowterminator = '\n',	fieldquote = '"')

create table [master].[dbo].[lu_weights_disease] --(93 rows affected)
	(weight			decimal(7,5)
	,disease_number	char(2))
  bulk insert [master].[dbo].[lu_weights_disease]
  from 'c:\cfi_sql\lu_weights_disease.txt'		with (firstrow = 2, fieldterminator = '\t', rowterminator = '\n')

--special read in for lu_px.txt accounting for a comment every other row
if (object_id('tempdb..#pxlookup') is not null) drop table #pxlookup
create table #pxlookup (data varchar(max))
  bulk insert #pxlookup 
  from 'c:\cfi_sql\lu_px.txt'					with (firstrow = 3, fieldterminator = '\t', rowterminator = '\n')

if (object_id('tempdb..#pxlookupxml') is not null) drop table #pxlookupxml
create table #pxlookupxml (id int not null identity(1,1) primary key clustered, data xml)
  insert #pxlookupxml (data)
	select cast('<r><d>' + replace(data, char(9), '</d><d>') + '</d></r>' as xml)
  from #pxlookup

create table[master].[dbo].[lu_px] --(45 rows affected)
	(px_start		char(5)
    ,px_stop		char(5)
    ,disease_number char(2))
  insert into [master].[dbo].[lu_px] (px_start, px_stop, disease_number)
	select	 d.data.value('(/r//d)[1]', 'varchar(5)') as px_start
			,d.data.value('(/r//d)[2]', 'varchar(5)') as px_stop
			,d.data.value('(/r//d)[3]', 'varchar(2)') as disease_number
  from #pxlookupxml as d
  where d.data.value('(/r//d)[1]', 'varchar(5)') not like ('#%')
 


 --read in patient list
create table [master].[dbo].[pat_ids] --(17642 rows affected)
	(patid char(32))
  bulk insert [master].[dbo].[pat_ids] 
  from 'c:\cfi_sql\pat_ids.txt'		with (firstrow = 2, rowterminator = '\n')



--read in patient claims
create table [master].[dbo].[pat_dx9] --(171220 rows affected)
	(patid	char(32)
	,dx9	char(10))
  bulk insert [master].[dbo].[pat_dx9]
  from 'c:\cfi_sql\pat_dx9.txt'		with (firstrow = 2, fieldterminator = '\t', rowterminator = '\n')

create table [master].[dbo].[pat_dx10] --(271945 rows affected)
	(patid	char(32)
	,dx10	char(10))
  bulk insert [master].[dbo].[pat_dx10]
  from 'c:\cfi_sql\pat_dx10.txt'	with (firstrow = 2, fieldterminator = '\t', rowterminator = '\n')

create table [master].[dbo].[pat_px] --(446356 rows affected)
	(patid	char(32)
	,px		char(5))
  bulk insert [master].[dbo].[pat_px]
  from 'c:\cfi_sql\pat_px.txt'		with (firstrow = 2, fieldterminator = '\t', rowterminator = '\n')
  


--create weight tables
select   a.disease_number
		,a.dx
		,b.weight
into [master].[dbo].[lu_weights_dx9] --(6960 rows affected)
from [master].[dbo].[lu_dx9] as a
	,[master].[dbo].[lu_weights_disease] as b 
where a.disease_number=b.disease_number

select   a.disease_number
		,a.dx
		,b.weight
into [master].[dbo].[lu_weights_dx10] --(27621 rows affected)
from [master].[dbo].[lu_dx10] as a
	,[master].[dbo].[lu_weights_disease] as b 
where a.disease_number=b.disease_number

select   a.disease_number
		,a.px_start
		,a.px_stop
		,b.weight
into [master].[dbo].[lu_weights_px]	--(45 rows affected)
from [master].[dbo].[lu_px] as a
	,[master].[dbo].[lu_weights_disease] as b 
where a.disease_number=b.disease_number



--score patient claims
select	 a.patid
		,b.disease_number
		,b.weight
into [master].[dbo].[pat_scores_dx9] --(134969 rows affected)
from [master].[dbo].[pat_dx9] as a 
left join 
	 [master].[dbo].[lu_weights_dx9] as b
      on a.dx9 = b.dx
where	 b.weight is not null --data cleaning
order by a.patid
		,b.disease_number

select 	 a.patid
		,b.disease_number 
		,b.weight
into	  [master].[dbo].[pat_scores_dx10] --(200504 rows affected)
from	  [master].[dbo].[pat_dx10] as a 
left join [master].[dbo].[lu_weights_dx10] as b
      on a.dx10 = b.dx
where	 b.weight is not null
order by a.patid
		,b.disease_number

select	 a.patid
		,b.disease_number
		,b.weight
into	  [master].[dbo].[pat_scores_px] --(96546 rows affected)
from	  [master].[dbo].[pat_px] as a 
left join [master].[dbo].[lu_weights_px] as b
      on a.px >= b.px_start and a.px <= b.px_stop
where	 b.weight is not null
	and  (substring(trim(a.px), 5, 1) like '[0-9]' or substring(trim(a.px), 5, 1) like '') --last_d(igit) criteria
order by a.patid
		,b.disease_number
		


--aggregate claims
select distinct --one disease number per person
		 a.patid
		,  disease_number
		,  weight
into	  [master].[dbo].[pat_scores_claims] --(225116 rows affected)
from	  [master].[dbo].[pat_ids] as a
left join(
	select * from [master].[dbo].[pat_scores_px]
	union 
	select * from [master].[dbo].[pat_scores_dx9]
	union 
	select * from [master].[dbo].[pat_scores_dx10]
	union 
	select patid, '0' as disease_number, 0.10288 as weight from [master].[dbo].[pat_ids] --adds required intercept of the cfi model to everyone
) as b
	on   a.patid = b.patid
order by a.patid
		,  disease_number



--sum weights to create patient frailty score
select	 patid
		,convert(decimal(7,5),sum(weight)) as score
into [master].[dbo].[pat_scores_cfi] --(17642 rows affected)
from [master].[dbo].[pat_scores_claims]
group by patid
order by patid
